﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace HSIA
{
    public class DatabaseObject
    {
        string strXMLDir;
        public EventHandle eventHandle;
        public DataTable dtSystemParam;
        public DataTable dtLanguage;
        public DataTable dtMeasureType;
        public DataTable dtSensorTitle;
        public DataTable dtLocationTitle;
        public DataRow drLoginUser;

        private string ConnectionString = "Data Source=localhost\\sqlexpress;Initial Catalog={0};User ID={1};Password={2};";//Integrated Security=SSPI;
        //private string strMasterConnect = "Data Source=localhost\\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;";

        public DatabaseObject()
        {
            strXMLDir = string.Format(ConnectionString, PublicFunc.sDatabase, "", "");
            eventHandle = new EventHandle();
            Refresh_AllDt();
        }

        public DatabaseObject(string sConn)
        {
            strXMLDir = sConn;
            eventHandle = new EventHandle();
            Refresh_AllDt();
        }

        public DatabaseObject(string sUser, string sPassword)
        {
            if ((sUser == "") && (sPassword == "")) strXMLDir = "Data Source=localhost\\sqlexpress;Initial Catalog=" + PublicFunc.sDatabase + ";Integrated Security=SSPI;";
            else strXMLDir = string.Format(ConnectionString, PublicFunc.sDatabase, sUser, sPassword);
            AttachDB(PublicFunc.sDatabase);
            eventHandle = new EventHandle();
            Refresh_AllDt();
        }

        private SqlConnection GetMyConnection()
        {
            SqlConnection sc = null;
            try
            {
                sc = new SqlConnection(strXMLDir);
                sc.Open();
            }
            catch (Exception e)
            {
                throw new CEventLog("建立数据库连接出错!", e);  //ConnectionString+" * "+
            }
            return sc;
        }

        private SqlConnection GetMasterConnection()
        {
            SqlConnection oc = null;
            try
            {
                oc = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;");
                oc.Open();
            }
            catch (Exception e)
            {
                throw new CEventLog(GetLanguage(104), e);  //ConnectionString+" * "+建立数据库连接出错!
            }
            return oc;
        }


        public bool AttachDB(string sDatabase)
        {
            string strMDF = Application.StartupPath + "\\database\\" + sDatabase + ".mdf";
            string strLDF = Application.StartupPath + "\\database\\" + sDatabase + "_log.ldf";
            try
            {
                if (!Directory.Exists(Application.StartupPath + "\\database")) Directory.CreateDirectory(Application.StartupPath + "\\database");
                if (!File.Exists(strMDF)) File.Copy(Application.StartupPath + "\\" + sDatabase + ".mdf", strMDF, true);
                if (!File.Exists(strLDF)) File.Copy(Application.StartupPath + "\\" + sDatabase + "_log.ldf", strLDF, true);
            }
            catch
            {
            }
            //string sSQL="select * From master.dbo.sysdatabases where name='"+sDatabase+"'";
            string sSQL = "select   [filename]   from   master..sysdatabases   where   name='" + sDatabase + "'";
            DataTable dt = GetDataTableFromMaster(sSQL, "aa");
            if (dt.Rows.Count > 0) return false;
            if ((File.Exists(strMDF)) && (File.Exists(strLDF)))
            {
                return AttachDB(sDatabase, strMDF, strLDF);
            }
            else
            {
                if (PublicFunc.sLanguage == "CN")
                    MessageBox.Show("不存在数据库文件,请查找原因!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                else
                    MessageBox.Show("Databse File is not exist!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
                return false;
            }
        }

        public bool AttachDB(string vDB, string vMDFFile, string vLDFFile)
        {
            bool bRet = true; ;
            string str = "EXEC sp_attach_db @dbname = '" + vDB + "', @filename1 = '" + vMDFFile + "',@filename2='" + vLDFFile + "'";
            SqlConnection sc = GetMasterConnection();
            SqlCommand com = new SqlCommand(str, sc);  //"insert into Image(ID,FileName,Image) values('"+strID+"','"+strFileName+"',@vImage)"

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                bRet = false;
                if (PublicFunc.sLanguage == "CN")
                    throw new CEventLog("附加数据库出错!", e);
                else
                    throw new CEventLog("Attach Database Error !", e);
            }
            sc.Close();

            return bRet;
        }

        public bool DetachDB(string vDB)
        {
            bool bRet = true; ;
            string str = "EXEC sp_detach_db '" + vDB + "', 'true'";
            SqlConnection sc = GetMyConnection();
            SqlCommand com = new SqlCommand(str, sc);  //"insert into Image(ID,FileName,Image) values('"+strID+"','"+strFileName+"',@vImage)"

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                bRet = false;
                if (PublicFunc.sLanguage == "CN") throw new CEventLog("分离数据库出错!", e);
                else throw new CEventLog("Detach Database Error !", e);
            }
            sc.Close();

            return bRet;
        }

        public bool DropDB(string vDB)
        {
            bool bRet = true; ;
            string str = "Drop DataBase " + vDB;
            SqlConnection sc = GetMyConnection();
            SqlCommand com = new SqlCommand(str, sc);

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                bRet = false;
                if (PublicFunc.sLanguage == "CN") throw new CEventLog("删除数据库出错!", e);
                else throw new CEventLog("Drop Database Error !", e);
            }
            sc.Close();

            return bRet;
        }

        public void RefreshSystemParam()
        {
            dtSystemParam = GetDataTableBySql("select * from SystemParam", "SystemParam");
            dtSystemParam.PrimaryKey = new DataColumn[] { dtSystemParam.Columns["Name"] };
        }

        public void RefreshLanguage()
        {
            dtLanguage = GetDataTableBySql("select * from Language", "Language");
            dtLanguage.PrimaryKey = new DataColumn[] { dtLanguage.Columns["ID"] };
        }

        public void RefreshMeasureType()
        {
            dtMeasureType = GetDataTableBySql("select * from MeasureType order by FieldIndex", "MeasureType");
            dtMeasureType.PrimaryKey = new DataColumn[] { dtMeasureType.Columns["FieldIndex"] };
            string dbo_Vag = "";
            string dbo_Measure = "";
            string dbo_pMeasure = "";
            string dbo_W = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                string sWarnType = Convert.ToString(int.Parse(dr["WarnType"].ToString()), 2).PadLeft(4, '0');

                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {

                    string sCnum = (iCnum != 1) ? "_" + i : "";
                    string sColumn = dr["ID"] + sCnum;

                    dbo_Vag += "MAX(" + sColumn + ") as max" + sColumn + ",MIN(" + sColumn + ") as min" + sColumn + ",";

                    dbo_Measure += "{0}" + dr["ID"] + sCnum + "{1},";
                    if (sWarnType[0].ToString() == "1") dbo_pMeasure += "U" + sColumn + ",";
                    if (sWarnType[1].ToString() == "1") dbo_pMeasure += "UP" + sColumn + ",";
                    if (sWarnType[2].ToString() == "1") dbo_pMeasure += "LP" + sColumn + ",";
                    if (sWarnType[3].ToString() == "1") dbo_pMeasure += "L" + sColumn + ",";
                }

                dbo_W += "0";
            }
            //dbo_Measure += "{0}W ";
            PublicFunc.dbo_Vag = dbo_Vag.TrimEnd(',');
            PublicFunc.dbo_Measure = dbo_Measure.TrimEnd(',');
            PublicFunc.dbo_pMeasure = dbo_pMeasure.TrimEnd(',');
            PublicFunc.dbo_W = dbo_W;
        }

        public void RefreshSensorTitle()
        {
            dtSensorTitle = GetDataTableBySql("select * from SensorTitle", "SensorTitle");
            dtSensorTitle.PrimaryKey = new DataColumn[] { dtSensorTitle.Columns["ID"] };
        }

        public void RefreshLocationTitle()
        {
            dtLocationTitle = GetDataTableBySql("select * from LocationTitle order by FieldIndex", "LocationTitle");
            dtLocationTitle.PrimaryKey = new DataColumn[] { dtLocationTitle.Columns["ID"] };
            string dbo_LocationTitle = "";
            foreach (DataRow dr in dtLocationTitle.Rows)
            {
                dbo_LocationTitle += dr["ID"].ToString() + ",";
            }
            PublicFunc.dbo_LocationTitle = dbo_LocationTitle.TrimEnd(',');
        }

        public void Refresh_AllDt()
        {
            RefreshLanguage();
            RefreshSystemParam();
            RefreshMeasureType();
            RefreshSensorTitle();
            RefreshLocationTitle();
            //Units.GetUnits(this);
        }

        public string GetLanguage(object oKey)
        {
            if (oKey == null || oKey == DBNull.Value || oKey.ToString() == "") return "";
            int iKey = 0;
            if (!int.TryParse(oKey.ToString(), out iKey)) return "";
            string strGetName = "";

            if (dtLanguage.Rows.Count > 0)
            {
                DataRow drLanguage = dtLanguage.Rows.Find(iKey);
                if (drLanguage != null) strGetName = drLanguage[PublicFunc.sLanguage].ToString();
            }
            return strGetName;
        }

        /// <summary>
        /// oKey为搜索键值， iType：1测量名称，2测量单位，3测量名称（单位）
        /// </summary>
        /// <returns></returns>
        public string GetMeasureName(object oKey, int iType)
        {
            if (oKey == null || oKey == DBNull.Value || oKey.ToString() == "") return "";
            int iKey = 0;
            if (!int.TryParse(oKey.ToString(), out iKey)) return "";
            string strGetName = "";

            if (dtMeasureType.Rows.Count > 0)
            {
                DataRow drMeasureType = dtMeasureType.Rows.Find(iKey);
                if (drMeasureType != null)
                {
                    switch (iType)
                    {
                        case 0:
                            strGetName = drMeasureType["ID"].ToString();
                            break;
                        case 1:
                            strGetName = drMeasureType["Name"].ToString();
                            break;
                        case 2:
                            strGetName = drMeasureType["Unit"].ToString();
                            break;
                        case 3:
                            strGetName = drMeasureType["Name"].ToString() + "(" + drMeasureType["Unit"].ToString() + ")";
                            break;
                    }
                }
            }
            return strGetName;
        }

        public double GetMeasureValue(object oKey, string sName)
        {
            double dValue = double.MaxValue;
            if (oKey == null || oKey == DBNull.Value || oKey.ToString() == "") return dValue;
            int iKey = 0;
            if (!int.TryParse(oKey.ToString(), out iKey)) return dValue;

            if (dtMeasureType.Rows.Count > 0)
            {
                DataRow drMeasureType = dtMeasureType.Rows.Find(iKey);
                if (drMeasureType != null)
                {
                    if (!double.TryParse(drMeasureType[sName].ToString(), out dValue)) return dValue;
                }
            }
            return dValue;
        }

        public string GetAddSQLFromHashtable(Hashtable ht, string strTable)
        {
            string strRet = "";
            if ((ht == null) || (ht.Count == 0)) return strRet;

            //StringBuilder sb = new StringBuilder();
            StringBuilder sbField = new StringBuilder("insert into " + strTable + "(");//ID");
            StringBuilder sbValue = new StringBuilder("values(");// + strID + "'");
            bool blFirst = true;
            foreach (DictionaryEntry dee in ht)
            {
                //if (blRefreshDataRow) dr[dee.Key.ToString()] = dee.Value.ToString();
                if (blFirst)
                {
                    if (dee.Value.ToString() == "") continue;
                    sbField.Append(dee.Key.ToString());
                    if (dee.Value.ToString().Substring(0, 1) == "@")
                        sbValue.Append(dee.Value.ToString());
                    else
                        sbValue.Append("'" + dee.Value.ToString() + "'");
                    blFirst = false;
                }
                else
                {
                    sbField.Append("," + dee.Key.ToString());
                    if (dee.Value == null)
                    {
                        sbValue.Append(",null");
                        continue;
                    }
                    if (dee.Value.ToString() == "")
                    {
                        sbValue.Append(",''");
                        continue;
                    }
                    if (dee.Value.ToString().Substring(0, 1) == "@")
                        sbValue.Append("," + dee.Value.ToString());
                    else
                        sbValue.Append(",'" + dee.Value.ToString() + "'");
                }
            }
            sbField.Append(") " + sbValue.ToString() + ")");
            strRet = sbField.ToString();

            return strRet;
        }

        public string GetModifySQLFromHashtable(Hashtable ht, string strTable, string strKey, string strValue)
        {
            string strRet = "";
            if ((ht == null) || (ht.Count == 0)) return strRet;

            StringBuilder sb = new StringBuilder("update " + strTable + " set ");
            bool blFirst = true;
            foreach (DictionaryEntry dee in ht)
            {
                if (blFirst)
                {
                    if (dee.Value.ToString().Substring(0, 1) == "@")
                        sb.Append(dee.Key.ToString() + "=" + dee.Value.ToString());
                    //sbValue.Append(dee.Value.ToString());
                    else
                        //sbValue.Append("'" + dee.Value.ToString() + "'");
                        sb.Append(dee.Key.ToString() + "='" + dee.Value.ToString() + "'");
                    blFirst = false;
                }
                else
                {
                    if (dee.Value.ToString().Substring(0, 1) == "@")
                        sb.Append("," + dee.Key.ToString() + "=" + dee.Value.ToString());
                    //sbValue.Append(dee.Value.ToString());
                    else
                        sb.Append("," + dee.Key.ToString() + "='" + dee.Value.ToString() + "'");
                }
            }
            sb.Append(" where " + strKey + "='" + strValue + "'");
            strRet = sb.ToString();

            return strRet;
        }

        public string GetSQLFromHashtable(Hashtable ht, string strTable, string strKey, string strValue, int intType)
        {
            string strRet = "";
            if ((ht == null) || (ht.Count == 0)) return strRet;

            if (intType == 0)  //intType 编辑类型 0:新增,１:修改
            {
                //StringBuilder sb = new StringBuilder();
                StringBuilder sbField = new StringBuilder("insert into " + strTable + "(");//ID");
                StringBuilder sbValue = new StringBuilder("values(");// + strID + "'");
                bool blFirst = true;
                foreach (DictionaryEntry dee in ht)
                {
                    //if (blRefreshDataRow) dr[dee.Key.ToString()] = dee.Value.ToString();
                    if (blFirst)
                    {
                        if (dee.Value.ToString() == "") continue;
                        sbField.Append(dee.Key.ToString());
                        if (dee.Value.ToString().Substring(0, 1) == "@")
                            sbValue.Append(dee.Value.ToString());
                        else
                            sbValue.Append("'" + dee.Value.ToString() + "'");
                        blFirst = false;
                    }
                    else
                    {

                        sbField.Append("," + dee.Key.ToString());
                        if (dee.Value == null)
                        {
                            sbValue.Append(",null");
                            continue;
                        }
                        if (dee.Value.ToString() == "")
                        {
                            sbValue.Append(",''");
                            continue;
                        }
                        if (dee.Value.ToString().Substring(0, 1) == "@")
                            sbValue.Append("," + dee.Value.ToString());
                        else
                            sbValue.Append(",'" + dee.Value.ToString() + "'");
                    }
                }
                sbField.Append(") " + sbValue.ToString() + ")");
                strRet = sbField.ToString();
            }
            else if (intType == 1)
            {
                StringBuilder sb = new StringBuilder("update " + strTable + " set ");
                bool blFirst = true;
                foreach (DictionaryEntry dee in ht)
                {
                    if (blFirst)
                    {
                        if (dee.Value.ToString().Substring(0, 1) == "@")
                            sb.Append(dee.Key.ToString() + "=" + dee.Value.ToString());
                        //sbValue.Append(dee.Value.ToString());
                        else
                            //sbValue.Append("'" + dee.Value.ToString() + "'");
                            sb.Append(dee.Key.ToString() + "='" + dee.Value.ToString() + "'");
                        blFirst = false;
                    }
                    else
                    {
                        if (dee.Value.ToString().Substring(0, 1) == "@")
                            sb.Append("," + dee.Key.ToString() + "=" + dee.Value.ToString());
                        //sbValue.Append(dee.Value.ToString());
                        else
                            sb.Append("," + dee.Key.ToString() + "='" + dee.Value.ToString() + "'");
                    }
                }
                sb.Append(" where " + strKey + "='" + strValue + "'");
                strRet = sb.ToString();
            }

            return strRet;
        }

        public string InsertImageByHashtable(string strSQL, Hashtable ht)// string strFileName, byte[] imageBytes)
        {
            string strRet = "OK";
            SqlConnection sc = GetMyConnection();
            SqlCommand com = new SqlCommand(strSQL, sc);
            foreach (DictionaryEntry de in ht)
            {
                com.Parameters.Add(de.Key.ToString(), SqlDbType.VarBinary);
                com.Parameters[de.Key.ToString()].Value = (byte[])de.Value;
            }

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                if (PublicFunc.sLanguage == "CN") throw new CEventLog("存储图象报错!", e);
                else throw new CEventLog("Save image Error!", e);
            }
            sc.Close();

            return strRet;
        }

        public string InsertImageByXML(string strSQL, Hashtable ht)// string strFileName, byte[] imageBytes)
        {
            string strRet = "OK";
            SqlConnection sc = GetMyConnection();
            SqlCommand com = new SqlCommand(strSQL, sc);
            foreach (DictionaryEntry de in ht)
            {
                com.Parameters.Add(de.Key.ToString(), SqlDbType.VarBinary);
                com.Parameters[de.Key.ToString()].Value = de.Value;
            }

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                if (PublicFunc.sLanguage == "CN") throw new CEventLog("存储图象报错!", e);
                else throw new CEventLog("Save image Error!", e);
            }
            sc.Close();

            return strRet;
        }

        public string InsertTableWithImage(string strSQL, Hashtable htImageBytes)  //byte[] imageBytes
        {
            string strRet = "OK";
            SqlConnection sc = GetMyConnection();
            SqlCommand com = new SqlCommand(strSQL, sc);  //"insert into Image(ID,FileName,Image) values('" + strID + "','" + strFileName + "',@vImage)"
            if (htImageBytes != null)
            {
                foreach (DictionaryEntry de in htImageBytes)
                {
                    com.Parameters.Add(de.Key.ToString(), SqlDbType.VarBinary);
                    com.Parameters[de.Key.ToString()].Value = de.Value;
                }
            }

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                if (PublicFunc.sLanguage == "CN") throw new CEventLog("存储图象报错!", e);
                else throw new CEventLog("Save image Error!", e);
            }
            sc.Close();

            return strRet;
        }

        public byte[] GetImage(string strKey, int intKey) //intKey : 0/ID,1/文件名
        {
            byte[] imageBytes = null;
            SqlConnection sc = GetMyConnection();
            string strSQL = "";
            if (intKey == 0)
                strSQL = "select image from image where id='" + strKey + "'";
            else if (intKey == 1)
                strSQL = "select image from image where filename='" + strKey + "'";

            SqlCommand com = new SqlCommand(strSQL, sc);
            SqlDataReader dr = com.ExecuteReader();

            dr.Read();
            imageBytes = (byte[])dr["Image"];
            return imageBytes;
        }

        public byte[] GetSingleImage(string strTable, string strKey, string strValue, string strField) //intKey : 0/ID,1/文件名
        {
            //string strRet = "OK";
            byte[] imageBytes = null;
            string strSQL = "";
            try
            {
                SqlConnection sc = GetMyConnection();
                strSQL = "select " + strField + " from " + strTable + " where " + strKey + "='" + strValue + "'";

                SqlCommand com = new SqlCommand(strSQL, sc);
                SqlDataReader dr = com.ExecuteReader();

                dr.Read();
                imageBytes = (byte[])dr[strField];
            }
            catch (Exception e)
            {
                throw new CEventLog(strSQL + ",获取图象报错!", e);
                return null;
                //strRet = e.Message;
            }

            return imageBytes;
        }

        public DataTable GetDataTable(DataTable dtDataViewSQL, string psName)
        {
            DataTable dt = null;
            DataRow dr = dtDataViewSQL.Rows.Find(psName);
            if (dr != null)
            {
                dt = ((DataView)dr["DataView"]).Table;
            }
            return dt;
        }

        public DataTable GetDataTableFromMaster(string strSQL, string strName)
        {
            if (strSQL == "") return null;
            DataTable dt = new DataTable(strName);
            SqlDataAdapter da = null;
            SqlConnection sc = GetMasterConnection();

            try
            {
                if (strSQL.Trim() == "")
                    da = new SqlDataAdapter("select * from " + strName, sc);
                else
                    da = new SqlDataAdapter(strSQL, sc);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new CEventLog(strSQL + "获取数据报错!", e);
            }
            finally
            {
                da.Dispose();
                sc.Close();
            }
            return dt;
        }

        public DataTable GetDataTableBySql(string strSQL, string strName)
        {
            if (strSQL == "") return null;
            DataTable dt = new DataTable(strName);
            SqlDataAdapter da = null;
            SqlConnection sc = GetMyConnection();

            try
            {
                if (strSQL.Trim() == "")
                    da = new SqlDataAdapter("select * from " + strName, sc);
                else
                    da = new SqlDataAdapter(strSQL, sc);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                if (strName == "SystemParam" && PublicFunc.iNetConnect == 0)
                {
                    if (!DetachDB(PublicFunc.sDatabase))
                        if (DropDB(PublicFunc.sDatabase)) throw new CEventLog("[DropDB!]", e);
                    AttachDB(PublicFunc.sDatabase);
                }
                else throw new CEventLog(strSQL + "[GetData" + strName + "]", e);
            }
            finally
            {
                da.Dispose();
                sc.Close();
            }
            return dt;
        }

        public string GetSQLFromDataTable(DataTable dt, string strTable, string strKey, int piStatus)
        {
            //DataTable dt = dv.Table;
            string strStatus = piStatus.ToString();
            StringBuilder sb = new StringBuilder("");
            StringBuilder sbColumn = new StringBuilder("");
            bool blHasQuoted = HasQuoted(dt.Columns[strKey]);
            string strKeyQuote = "";
            string strQuote = "";
            if (blHasQuoted) strKeyQuote = "'";

            //Delete
            DataRow[] delRows = dt.Select(null, null, DataViewRowState.Deleted);
            foreach (DataRow dr in delRows)
            {
                if (dr[strStatus, DataRowVersion.Original].ToString().ToString() == "1")
                {
                    sb.Append("delete from " + strTable + " where " + strKey + "=");
                    sb.Append(strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote);
                    sb.Append(";");
                }
                else if (dr[strStatus, DataRowVersion.Original].ToString().ToString() == "0")
                {
                    sb.Append("update " + strTable + " set " + strStatus + "=3  where " + strKey + "=");
                    sb.Append(strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote);
                    sb.Append(";");
                }
            }

            //Add            
            DataRow[] addRows = dt.Select(null, null, DataViewRowState.Added);
            if (addRows.Length > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    if (sbColumn.ToString() == "")
                    {
                        sbColumn.Append("(");
                        //sbColumn.Append("(\"");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                        //str = "\"" + dt.Columns[i].ColumnMapping + "\"";
                    }
                    else
                    {
                        //sbColumn.Append(",\"");
                        sbColumn.Append(",");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                    }
                }
                string strColumn = "insert into " + strTable + sbColumn.ToString() + ") ";
                //sb.Append("insert into " + strTable + sbColumn.ToString() + ")");
                //string strColumn = sbColumn.ToString() + ")";
                foreach (DataRow dr in addRows)
                {
                    //sb.Append("insert into " + strTable + strColumn);
                    sbColumn.Remove(0, sbColumn.Length);
                    foreach (DataColumn dc in dt.Columns)
                    {
                        blHasQuoted = HasQuoted(dc);
                        if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            sbColumn.Append(" values(");
                            sbColumn.Append(strQuote);
                            sbColumn.Append(dr[dc].ToString());
                            sbColumn.Append(strQuote);
                        }
                        else
                        {
                            sbColumn.Append("," + strQuote);
                            sbColumn.Append(dr[dc].ToString());
                            sbColumn.Append(strQuote);
                        }

                    }
                    sb.Append(strColumn + sbColumn.ToString() + ");");
                    //sb.Append("delete from " + strTable + " where " + strKey + "=\"" + dr[strKey, DataRowVersion.Original].ToString() + "\"");
                    //sb.AppendLine();
                }
            }

            //Modify
            //sbColumn.Remove(0, sbColumn.Length);
            DataRow[] modRows = dt.Select(null, null, DataViewRowState.ModifiedCurrent);
            if (modRows.Length > 0)
            {
                foreach (DataRow dr in modRows)
                {
                    sbColumn.Remove(0, sbColumn.Length);
                    //sb.Append("update "+strTable+"set ");
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dr[dc, DataRowVersion.Original] == dr[dc, DataRowVersion.Current])
                            continue;
                        blHasQuoted = HasQuoted(dc);
                        if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            //if (dr[dc, DataRowVersion.Original] != dr[dc, DataRowVersion.Current])
                            sbColumn.Append(dc.ColumnName + "=" + strQuote + dr[dc, DataRowVersion.Current].ToString() + strQuote);
                        }
                        else
                        {
                            sbColumn.Append("," + dc.ColumnName + "=" + strQuote + dr[dc, DataRowVersion.Current].ToString() + strQuote);
                        }
                    }
                    sb.Append("update " + strTable + " set " + sbColumn.ToString() + " where " + strKey + "=" + strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote + ";");
                    //sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        public string GetSQLFromDataTable(DataTable dt, string strTable, string strKey, string sExclude, string sInclude)
        {
            //DataTable dt = dv.Table;
            StringBuilder sb = new StringBuilder("");
            StringBuilder sbColumn = new StringBuilder("");
            if (dt.Columns.IndexOf(strKey) < 0)
            {
                MessageBox.Show("GetSQLFromDataTable缺少主键,请与管理员联系!");
                return "";
            }
            bool blHasQuoted = HasQuoted(dt.Columns[strKey]);
            //string strKeyQuote = "";
            string strQuote = "'";
            //if (blHasQuoted) strKeyQuote = "'";

            //Delete
            DataRow[] delRows = dt.Select(null, null, DataViewRowState.Deleted);
            foreach (DataRow dr in delRows)
            {
                sb.Append("delete from " + strTable + " where " + strKey + "='" + dr[strKey, DataRowVersion.Original].ToString() + "';");
                //sb.Append(strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote);
                //sb.Append(";");
            }

            //Add            
            DataRow[] addRows = dt.Select(null, null, DataViewRowState.Added);
            if (addRows.Length > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (sExclude != "")
                    {
                        if (sExclude.IndexOf(dt.Columns[i].ColumnName) > -1) continue;
                    }
                    if (sInclude != "")
                    {
                        if (sInclude.IndexOf(dt.Columns[i].ColumnName) < 0) continue;
                    }
                    if (sbColumn.ToString() == "")
                    {
                        sbColumn.Append("(");
                        //sbColumn.Append("(\"");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                        //str = "\"" + dt.Columns[i].ColumnMapping + "\"";
                    }
                    else
                    {
                        //sbColumn.Append(",\"");
                        sbColumn.Append(",");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                    }
                }
                string strColumn = "insert into " + strTable + sbColumn.ToString() + ") ";
                //sb.Append("insert into " + strTable + sbColumn.ToString() + ")");
                //string strColumn = sbColumn.ToString() + ")";
                foreach (DataRow dr in addRows)
                {
                    //sb.Append("insert into " + strTable + strColumn);
                    sbColumn.Remove(0, sbColumn.Length);
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (sExclude != "")
                        {
                            if (sExclude.IndexOf(dc.ColumnName) > -1) continue;
                        }
                        if (sInclude != "")
                        {
                            if (sInclude.IndexOf(dc.ColumnName) < 0) continue;
                        }
                        //blHasQuoted = HasQuoted(dc);
                        //if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            sbColumn.Append(" values(");

                            if (dr[dc] != DBNull.Value)
                            {
                                //sbColumn.Append(strQuote);
                                sbColumn.Append("'" + dr[dc].ToString() + "'");
                                //sbColumn.Append(strQuote);
                            }
                            else
                                sbColumn.Append("null");
                        }
                        else
                        {
                            if (dr[dc] != DBNull.Value)
                            {
                                //sbColumn.Append("," + strQuote);
                                sbColumn.Append(",'" + dr[dc].ToString() + "'");
                                //sbColumn.Append(strQuote);
                            }
                            else
                            {
                                sbColumn.Append(",null");
                            }
                        }

                    }
                    sb.Append(strColumn + sbColumn.ToString() + ");");
                    //sb.Append("delete from " + strTable + " where " + strKey + "=\"" + dr[strKey, DataRowVersion.Original].ToString() + "\"");
                    //sb.AppendLine();
                }
            }

            //Modify
            //sbColumn.Remove(0, sbColumn.Length);
            DataRow[] modRows = dt.Select(null, null, DataViewRowState.ModifiedCurrent);
            if (modRows.Length > 0)
            {
                foreach (DataRow dr in modRows)
                {
                    sbColumn.Remove(0, sbColumn.Length);
                    //sb.Append("update "+strTable+"set ");
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dr[dc, DataRowVersion.Original].ToString() == dr[dc, DataRowVersion.Current].ToString()) continue;
                        if (sExclude != "")
                        {
                            if (sExclude.IndexOf(dc.ColumnName) > -1) continue;
                        }
                        if (sInclude != "")
                        {
                            if (sInclude.IndexOf(dc.ColumnName) < 0) continue;
                        }
                        //blHasQuoted = HasQuoted(dc);
                        //if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            //if (dr[dc, DataRowVersion.Original] != dr[dc, DataRowVersion.Current])
                            sbColumn.Append(dc.ColumnName + "='" + dr[dc, DataRowVersion.Current].ToString() + "'");
                        }
                        else
                        {
                            sbColumn.Append("," + dc.ColumnName + "='" + dr[dc, DataRowVersion.Current].ToString() + "'");
                        }
                    }
                    if (sbColumn.ToString().Trim() != "")
                        sb.Append("update " + strTable + " set " + sbColumn.ToString() + " where " + strKey + "='" + dr[strKey, DataRowVersion.Original].ToString() + "';");
                    //sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        public string GetSQLFromDataTable(DataTable dt, string strTable, string strKey)
        {
            //DataTable dt = dv.Table;
            StringBuilder sb = new StringBuilder("");
            StringBuilder sbColumn = new StringBuilder("");
            bool blHasQuoted = HasQuoted(dt.Columns[strKey]);
            string strKeyQuote = "";
            string strQuote = "";
            if (blHasQuoted) strKeyQuote = "'";

            //Delete
            DataRow[] delRows = dt.Select(null, null, DataViewRowState.Deleted);
            foreach (DataRow dr in delRows)
            {
                sb.Append("delete from " + strTable + " where " + strKey + "=");
                sb.Append(strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote);
                sb.Append(";");
            }

            //Add            
            DataRow[] addRows = dt.Select(null, null, DataViewRowState.Added);
            if (addRows.Length > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    if (sbColumn.ToString() == "")
                    {
                        sbColumn.Append("(");
                        //sbColumn.Append("(\"");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                        //str = "\"" + dt.Columns[i].ColumnMapping + "\"";
                    }
                    else
                    {
                        //sbColumn.Append(",\"");
                        sbColumn.Append(",");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                    }
                }
                string strColumn = "insert into " + strTable + sbColumn.ToString() + ") ";
                //sb.Append("insert into " + strTable + sbColumn.ToString() + ")");
                //string strColumn = sbColumn.ToString() + ")";
                foreach (DataRow dr in addRows)
                {
                    //sb.Append("insert into " + strTable + strColumn);
                    sbColumn.Remove(0, sbColumn.Length);
                    foreach (DataColumn dc in dt.Columns)
                    {
                        blHasQuoted = HasQuoted(dc);
                        if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            sbColumn.Append(" values(");

                            if (dr[dc] != DBNull.Value)
                            {
                                sbColumn.Append(strQuote);
                                sbColumn.Append(dr[dc].ToString());
                                sbColumn.Append(strQuote);
                            }
                            else
                                sbColumn.Append("null");
                        }
                        else
                        {
                            if (dr[dc] != DBNull.Value)
                            {
                                sbColumn.Append("," + strQuote);
                                sbColumn.Append(dr[dc].ToString());
                                sbColumn.Append(strQuote);
                            }
                            else
                            {
                                sbColumn.Append(",null");
                            }
                        }

                    }
                    sb.Append(strColumn + sbColumn.ToString() + ");");
                    //sb.Append("delete from " + strTable + " where " + strKey + "=\"" + dr[strKey, DataRowVersion.Original].ToString() + "\"");
                    //sb.AppendLine();
                }
            }

            //Modify
            //sbColumn.Remove(0, sbColumn.Length);
            DataRow[] modRows = dt.Select(null, null, DataViewRowState.ModifiedCurrent);
            if (modRows.Length > 0)
            {
                foreach (DataRow dr in modRows)
                {
                    sbColumn.Remove(0, sbColumn.Length);
                    //sb.Append("update "+strTable+"set ");
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dr[dc, DataRowVersion.Original] == dr[dc, DataRowVersion.Current])
                            continue;
                        blHasQuoted = HasQuoted(dc);
                        if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            //if (dr[dc, DataRowVersion.Original] != dr[dc, DataRowVersion.Current])
                            sbColumn.Append(dc.ColumnName + "=" + strQuote + dr[dc, DataRowVersion.Current].ToString() + strQuote);
                        }
                        else
                        {
                            sbColumn.Append("," + dc.ColumnName + "=" + strQuote + dr[dc, DataRowVersion.Current].ToString() + strQuote);
                        }
                    }
                    sb.Append("update " + strTable + " set " + sbColumn.ToString() + " where " + strKey + "=" + strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote + ";");
                    //sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        public string GetSQLFromDataTable(DataTable dt, string strTable, string strKey, string psExcludeField)
        {
            //DataTable dt = dv.Table;
            StringBuilder sb = new StringBuilder("");
            StringBuilder sbColumn = new StringBuilder("");
            bool blHasQuoted = HasQuoted(dt.Columns[strKey]);
            string strKeyQuote = "";
            string strQuote = "";
            if (blHasQuoted) strKeyQuote = "'";

            //Delete
            DataRow[] delRows = dt.Select(null, null, DataViewRowState.Deleted);
            foreach (DataRow dr in delRows)
            {
                sb.Append("delete from " + strTable + " where " + strKey + "=");
                sb.Append(strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote);
                sb.Append(";");
            }

            //Add            
            DataRow[] addRows = dt.Select(null, null, DataViewRowState.Added);
            if (addRows.Length > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (psExcludeField.IndexOf(dt.Columns[i].ColumnName) > -1) continue;
                    if (sbColumn.ToString() == "")
                    {
                        sbColumn.Append("(");
                        //sbColumn.Append("(\"");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                        //str = "\"" + dt.Columns[i].ColumnMapping + "\"";
                    }
                    else
                    {
                        //sbColumn.Append(",\"");
                        sbColumn.Append(",");
                        sbColumn.Append(dt.Columns[i].ColumnName);
                        //sbColumn.Append("\"");
                    }
                }
                string strColumn = "insert into " + strTable + sbColumn.ToString() + ") ";
                //sb.Append("insert into " + strTable + sbColumn.ToString() + ")");
                //string strColumn = sbColumn.ToString() + ")";
                foreach (DataRow dr in addRows)
                {
                    //sb.Append("insert into " + strTable + strColumn);
                    sbColumn.Remove(0, sbColumn.Length);
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (psExcludeField.IndexOf(dc.ColumnName) > -1) continue;

                        blHasQuoted = HasQuoted(dc);
                        if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            sbColumn.Append(" values(");

                            if (dr[dc] != DBNull.Value)
                            {
                                sbColumn.Append(strQuote);
                                sbColumn.Append(dr[dc].ToString());
                                sbColumn.Append(strQuote);
                            }
                            else
                                sbColumn.Append("null");
                        }
                        else
                        {
                            if (dr[dc] != DBNull.Value)
                            {
                                sbColumn.Append("," + strQuote);
                                sbColumn.Append(dr[dc].ToString());
                                sbColumn.Append(strQuote);
                            }
                            else
                            {
                                sbColumn.Append(",null");
                            }
                        }

                    }
                    sb.Append(strColumn + sbColumn.ToString() + ");");
                    //sb.Append("delete from " + strTable + " where " + strKey + "=\"" + dr[strKey, DataRowVersion.Original].ToString() + "\"");
                    //sb.AppendLine();
                }
            }

            //Modify
            //sbColumn.Remove(0, sbColumn.Length);
            DataRow[] modRows = dt.Select(null, null, DataViewRowState.ModifiedCurrent);
            if (modRows.Length > 0)
            {
                foreach (DataRow dr in modRows)
                {

                    sbColumn.Remove(0, sbColumn.Length);
                    //sb.Append("update "+strTable+"set ");
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (psExcludeField.IndexOf(dc.ColumnName) > -1) continue;
                        if (dr[dc, DataRowVersion.Original].ToString() == dr[dc, DataRowVersion.Current].ToString())
                            continue;
                        blHasQuoted = HasQuoted(dc);
                        if (blHasQuoted) strQuote = "'"; else strQuote = "";
                        if (sbColumn.ToString() == "")
                        {
                            //if (dr[dc, DataRowVersion.Original] != dr[dc, DataRowVersion.Current])
                            sbColumn.Append(dc.ColumnName + "=" + strQuote + dr[dc, DataRowVersion.Current].ToString() + strQuote);
                        }
                        else
                        {
                            sbColumn.Append("," + dc.ColumnName + "=" + strQuote + dr[dc, DataRowVersion.Current].ToString() + strQuote);
                        }
                    }
                    sb.Append("update " + strTable + " set " + sbColumn.ToString() + " where " + strKey + "=" + strKeyQuote + dr[strKey, DataRowVersion.Original].ToString() + strKeyQuote + ";");
                    //sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        public string ExcuteSQL(string strSQL)
        {
            if (strSQL == "")
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新!";
            }
            char cSpliter = ';';
            string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;

            try
            {
                foreach (string str in sl)
                {
                    //SqlCommand cd = new SqlCommand(str, sc);
                    if (str == "") continue;
                    cd.CommandType = CommandType.Text;
                    cd.CommandText = str;
                    cd.ExecuteNonQuery();
                }
                strRet = "OK";
                st.Commit();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                st.Rollback();
                throw new CEventLog(strSQL + "更新数据表报错!", e);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string BackupSQL(string strSQL)
        {

            if (strSQL == "")
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新!";
            }
            string strRet = "";
            string strConn = "Data Source=.\\sqlexpress;Initial Catalog=Master;Integrated Security=SSPI;";
            SqlConnection sc = new SqlConnection(strConn);
            sc.Open();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;

            try
            {
                cd.CommandType = CommandType.Text;
                cd.CommandText = strSQL;
                cd.ExecuteNonQuery();
                strRet = "OK";
            }
            catch (Exception e)
            {
                strRet = e.Message;
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string ExcuteSQL(string strSQL, out int iCount)
        {
            iCount = 0;
            if (strSQL == "")
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新!";
            }
            //char cSpliter = ';';
            //string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;

            try
            {
                //foreach (string str in sl)
                //{
                //    //SqlCommand cd = new SqlCommand(str, sc);
                //    if (str == "") continue;
                cd.CommandType = CommandType.Text;
                cd.CommandText = strSQL;
                iCount = cd.ExecuteNonQuery();
                //}
                strRet = "OK";
                st.Commit();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                //st.Rollback();
                //throw new CEventLog(strSQL + "更新数据表报错!", e);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string ExcuteSQLByMaster(string strSQL)
        {

            if (strSQL == "")
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新!";
            }
            char cSpliter = ';';
            string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            //SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            //cd.Transaction = st;

            try
            {
                foreach (string str in sl)
                {
                    //SqlCommand cd = new SqlCommand(str, sc);
                    if (str == "") continue;
                    cd.CommandType = CommandType.Text;
                    cd.CommandText = str;
                    cd.ExecuteNonQuery();
                }
                strRet = "OK";
                //st.Commit();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                //st.Rollback();
                throw new CEventLog(strSQL + "更新数据表报错!", e);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string ExcuteSQL(string[] slSQL)
        {

            if (slSQL.Length == 0)
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新!";
            }
            //char cSpliter = ';';
            //string[] sl = strSQL.Split(cSpliter);

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;

            try
            {
                foreach (string str in slSQL)
                {
                    //SqlCommand cd = new SqlCommand(str, sc);
                    if (str == "") continue;
                    cd.CommandType = CommandType.Text;
                    cd.CommandText = str;
                    cd.ExecuteNonQuery();
                }
                strRet = "OK";
                st.Commit();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                st.Rollback();
                throw new CEventLog(slSQL.ToString() + "更新数据表报错!", e);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string BulkcopyDatatable(DataTable dt, string sTableName)
        {
            if (dt.Rows.Count == 0)
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新!";
            }

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(sc, SqlBulkCopyOptions.UseInternalTransaction, st);
            try
            {
                sqlbulkcopy.DestinationTableName = sTableName;//数据库中的表名 
                sqlbulkcopy.WriteToServer(dt);
            }
            catch (Exception e)
            {
                strRet = e.Message;
                st.Rollback();
                throw new CEventLog("表格更新数据表报错!", e);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public bool HasQuoted(DataColumn dc)
        {
            bool blRet = true;
            switch (dc.DataType.ToString())
            {
                case "System.Int32":
                case "System.Decimal":
                case "System.Double":
                case "System.Int64":
                case "System.Byte[]":
                case "System.Int16":
                    blRet = false;
                    break;
                default:
                    break;
            }
            return blRet;
        }

        public void SetXMLDir(string sXMLDir) //string vXMLConfigFile
        {
            strXMLDir = sXMLDir;
        }

        public void ExcuteSqlCommand(List<string[]> LSQL, string commandText)
        {
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            cd.Transaction = st;
            cd.CommandType = CommandType.StoredProcedure;
            string[] @ID = null;
            switch (commandText)
            {
                case "InsertDatas": @ID = PublicFunc.MergerArray(new string[] { "@GatherDate", "@GatherNo", "@SensorID", "@ReceiverNo", "@X", "@Y" }, string.Format(PublicFunc.dbo_Measure, "@", "").Split(',')); cd.CommandText = "InsertDatas"; break;
                case "UpdateDatas": @ID = PublicFunc.MergerArray(string.Format(PublicFunc.dbo_Measure, "@", "W").Split(','), new string[] { "@WarnType", "@ID" }); cd.CommandText = "UpdateDatas"; break;
                case "SensorXYs1": @ID = PublicFunc.MergerArray(new string[] { "@ID", "@GatherDate", "@X", "@Y" }, string.Format(PublicFunc.dbo_Measure, "@Last", "").Split(',')); cd.CommandText = "SensorXYs"; break;
                case "SensorXYs2": @ID = new string[] { "@W", "@ID" }; cd.CommandText = "SensorXYs"; break;
            }

            try
            {
                foreach (string[] s in LSQL)
                {
                    SqlParameter[] pCustomerID = new SqlParameter[s.Length];
                    for (int i = 0; @ID.Length > i; i++)//传递参数
                    {
                        pCustomerID[i] = new SqlParameter();
                        pCustomerID[i].SqlDbType = SqlDbType.VarChar;
                        pCustomerID[i].ParameterName = @ID[i];
                        pCustomerID[i].Value = (s[i] == "") ? null : s[i];
                        cd.Parameters.Add(pCustomerID[i]);
                    }
                    cd.ExecuteNonQuery();
                    cd.Parameters.Clear();
                }
                st.Commit();
            }
            catch (Exception e)
            {
                st.Rollback();
                throw new CEventLog("储存过程报错!", e);
            }
            finally
            {
                sc.Close();
            }

        }

        public string CreateDBO()
        {
            string sCommandText = "";
            string SqlStr = "ALTER PROC [dbo].[{0}]({1}) AS {2} ;";
            string sM = "";
            string sW = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {
                    sM += string.Format(",@{0} decimal(4, 1)=null", dr["ID"] + ((iCnum != 1) ? "_" + i : ""));
                }
            }
            sCommandText += string.Format(SqlStr, "InsertDatas", "@GatherDate varchar(20), @GatherNo varchar(20), @SensorID varchar(12), @ReceiverNo int, @X decimal(10, 6)=null, @Y decimal(10, 6)=nul" + sM, "insert into GatherData(GatherDate,GatherNo,SensorID,ReceiverNo,X,Y," + string.Format(PublicFunc.dbo_Measure, "", "") + ") values(@GatherDate,@GatherNo,@SensorID,@ReceiverNo,@X,@Y," + string.Format(PublicFunc.dbo_Measure, "@", "") + ")");
            sW = sM = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                sM += string.Format(",@{0}W decimal(4, 1)=null", dr["ID"]);
                sW += string.Format(",{0}W=isnull(@{0}W,{0}W)", dr["ID"]);
            }
            sCommandText += string.Format(SqlStr, "UpdateDatas", "@WarnType varchar(20)" + sM + ",@ID varchar(12)", "update GatherData set WarnType=@WarnType" + sW + " where ID=@ID");
            sW = sM = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {
                    string sCnum = (iCnum != 1) ? "_" + i : "";
                    sM += string.Format(",@Last{0} decimal(4, 1)=null", dr["ID"] + sCnum);
                    sW += string.Format(",Last{0}=isnull(@Last{0},Last{0})", dr["ID"] + sCnum);
                }
            }
            sCommandText += string.Format(SqlStr, "SensorXYs", "@GatherDate varchar(20)=null, @X decimal(10, 6)=null, @Y decimal(10, 6)=null" + sM + ",@W varchar(20)=null,@ID varchar(12)", "update SensorXY set GatherDate =isnull(@GatherDate,GatherDate), X=isnull(@X,X),Y=isnull(@Y,Y)" + sW + ",W=isnull(@W,W) where ID=@ID");

            return ExcuteSQL(sCommandText);
        }


    }

}
